/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package csse3004.GD.DA;

import csse3004.GD.PollAdminAccount;
import csse3004.GD.Poll;
import csse3004.GD.USTemp;

import csse3004.GD.SysAdmin.SysAdminService;

import java.util.logging.Logger;
import java.util.ArrayList;
import csse3004.GD.SysAdmin.AdminAccount;
import csse3004.GD.SysAdmin.ManagePA;
import csse3004.GD.SysAdmin.SystemReport;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.util.Calendar;
import java.util.Locale;

/**
 *
 * @author Tom McNair - S41758984
 */
public class SysAdminDA extends GenericDA implements SysAdminService {

    private static final Logger m_logger = Logger.getLogger(SysAdminDA.class.getName());

    //@Override
    public final ArrayList<AdminAccount> getPAdminDetails() {

        String sql = "SELECT User_ID, Expiry_Date FROM USER_EXPIRY WHERE Enabled = 'Yes'";

        ArrayList<AdminAccount> DateArray = new ArrayList<AdminAccount>();
        AdminAccount account = new AdminAccount();
        int userID;
        String expiryDate;

        m_logger.info(sql);
        
        try {
            ResultSet results = DatabaseQuery(sql);
            
            while (results.next()) {
                userID = results.getInt("User_ID");
                expiryDate = results.getString("Expiry_Date"); 
                account.setUserID(userID);
                account.setExpiryDate(expiryDate);
                DateArray.add(account);
            }
            return DateArray;

        } catch (Exception ex) {
            m_logger.info("getPAdminDetails() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    /** A function that returns the number of users in the system.
    @return numUser[] Returns an int Array containing the user totals;
     *      in order, Web, Master, Creator, Admin, SysAdmin
     */
    public final int[] getUserNumbers() {
        int[] userTotals = new int[6];

        String sql = "SELECT * FROM User_Totals";
        int counter = 0;

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                userTotals[counter] = results.getInt("Total");
                counter++;
            }

            return userTotals;
        } catch (Exception ex) {
            m_logger.info("getUserNumbers() failed\n");
            m_logger.info(ex.toString());
        }

        return null;
    }

    public final int getPollCount() {
        int pollCount = -1;

        String sql = "SELECT COUNT(*) FROM POLL";

        try {
            ResultSet results = DatabaseQuery(sql);
            results.next();
            pollCount = results.getInt(1);
        } catch (Exception ex) {
            m_logger.info("getPollCount() failed\n");
            m_logger.info(ex.toString());
        }

        return pollCount;
    }

    public int getActivePollCount() {
        int pollCount = -1;

        String sql = "SELECT COUNT(*) FROM POLLS WHERE Available = 'Yes'";

        try {
            ResultSet results = DatabaseQuery(sql);
            results.next();
            pollCount = results.getInt(1);
        } catch (Exception ex) {
            m_logger.info("getPollCount() failed\n");
            m_logger.info(ex.toString());
        }

        return pollCount;
    }

    public final ArrayList<USTemp> getUsersByLocation(String searchKey) {

        ArrayList<String> searchResults = new ArrayList<String>();
        String sql = "SELECT USERS.User_ID, USERS.USERNAME FROM User_Location a, USERS,Location b WHERE b.Name = '" + searchKey + "' AND a.Location_ID = b.Location_ID AND a.User_ID = USERS.User_ID AND Users.Enabled = 'Yes'";
        ArrayList<USTemp> USArray = new ArrayList<USTemp>();
        USTemp userSearch;

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                //searchResults.add(results.getString("User_ID"));
                userSearch = new USTemp();

                userSearch.setID(results.getInt("User_ID"));
                userSearch.setName(results.getString("USERNAME"));

                USArray.add(userSearch);
            }
            return USArray;
            //return searchResults;
        } catch (Exception ex) {
            m_logger.info("getUsersByLocation() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    public final ArrayList<USTemp> getUsersByOrganisation(String searchKey) {

        ArrayList<String> searchResults = new ArrayList<String>();
        String sql = "SELECT USERS.User_ID, USERS.USERNAME FROM USER_ORGANISATION a, USERS,ORGANISATION b WHERE b.Name = '" + searchKey + "' AND a.ORGANISATION_ID = b.ORGANISATION_ID AND a.User_ID = USERS.User_ID AND Users.Enabled = 'Yes'";
        ArrayList<USTemp> USArray = new ArrayList<USTemp>();
        USTemp userSearch;

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                //searchResults.add(results.getString("User_ID"));
                userSearch = new USTemp();

                userSearch.setID(results.getInt("User_ID"));
                userSearch.setName(results.getString("USERNAME"));

                USArray.add(userSearch);
            }
            return USArray;
            //return searchResults;
        } catch (Exception ex) {
            m_logger.info("getUsersByOrganisation() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    public final ArrayList<Poll> getPolls(int userID) {
        String sql = "SELECT Poll.Poll_ID, Poll.Name, Poll.Public_Result, "
                + "Poll.Anonymous, "
                + "Poll.Admin_ID FROM Poll, Poll_Master, Users "
                + "WHERE USERS.User_ID = Poll_Master.Master_ID AND "
                + "Poll.Poll_ID = Poll_Master.Poll_ID AND "
                + "Users.User_ID = " + userID;

        ArrayList<Poll> pollArray = new ArrayList<Poll>();
        Poll poll;

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                poll = new Poll();

                poll.setId(results.getInt("Poll_ID"));
                poll.setName(results.getString("Name"));

                pollArray.add(poll);
            }

            return pollArray;

        } catch (Exception ex) {
            m_logger.info("getPolls() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    public final ArrayList<PollAdminAccount> getPollAdminList() {
        String sql = "SELECT * FROM User_Expiry";
        m_logger.info(sql);
        ArrayList<PollAdminAccount> accountArray = new ArrayList<PollAdminAccount>();
        PollAdminAccount account;
        //Connection conn = null;

        try {
            ResultSet results = DatabaseQuery(sql);
            //ResultSet results = DatabaseQuery(sql, conn);

            while (results.next()) {
                account = new PollAdminAccount();

                account.setID(results.getInt("User_ID"));

                long expiry = results.getLong("Expiry_Date");
                
                Calendar date = new java.util.GregorianCalendar();
                date.setTimeInMillis(expiry);
                
                m_logger.info(date.toString());

                java.util.Date today = new java.util.Date();
                java.sql.Date sqlToday = new java.sql.Date(today.getTime());
                m_logger.info("Today's Date: "+sqlToday.toString());

                
                DateFormat dataformat =  DateFormat. getDateInstance(DateFormat.LONG);
                String dateString = dataformat.format(date.getTime());
 
                account.setExpiryDate(dateString);
                
                long diff = date.getTimeInMillis() - sqlToday.getTime();
                
                diff /= 86400000;
                
                account.setDaysLeft(diff);
                
                //int diff = expiry.compareTo(sqlToday);
                m_logger.info("Difference:"+diff+" Days");

                accountArray.add(account);
            }
            //conn.close();
            return accountArray;
        } catch (Exception ex) {
            m_logger.info("getPollAdminList() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }
    
    /** A function that retrieves number of users participants in a specific poll

    @param pollUserCount variable storing the number of users


    @return pollUserCount 
    */
    public final int getPollUserCount(int pollID) {
        String sql = "SELECT COUNT(*) AS 'Potatoes' FROM Users WHERE Poll.Poll_ID = Participant_List.Poll_ID "
                + "AND Participant_List.List_ID = Participant.List_ID AND "
                + "Participant.User_ID = Users.User_ID AND Poll.Poll_ID = " + pollID;
        
        int count = 0;
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                count = results.getInt("Potatoes");
            }
        } catch (Exception ex) {
            m_logger.info("getPollUserCount() failed");
            m_logger.info(ex.toString());
        }
        return count;
    }
    
    public final SystemReport getReportData() {
        SystemReport report = new SystemReport();
        String sql = "SELECT COUNT (*) AS \"pollCount\" FROM Poll";
        int pollCount = 0;
        int pollPending = 0;
        int pollOpen = 0;
        int pollClosed = 0;
        int status = 0;
        
        try {
            m_logger.info("0");
            
            sql = "SELECT Status FROM Poll";
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                pollCount++;
                status = results.getByte("Status");
                if (status == 0) {
                    pollPending++;
                } else if (status == 1) {
                    pollOpen++;
                } else {
                    pollClosed++;
                }
            }
            report.setPollTotal(pollCount);
            report.setPollPending(pollPending);
            report.setPollOpen(pollOpen);
            report.setPollComplete(pollClosed);
            
            m_logger.info("1");
            
            
//            sql = "SELECT COUNT (*) AS \"questionTotal\" FROM Question";
//            results = DatabaseQuery(sql);
//            results.next();
//            report.setQuestionTotal(results.getInt("questionTotal"));
//                        
//            m_logger.info("4");
//            
//
//            sql = "SELECT COUNT (*) AS \"questionMC\" FROM Question WHERE Type = 'MC'";
//            results = DatabaseQuery(sql);
//            results.next();
//            report.setQuestionMC(results.getInt("questionMC"));
//            report.setQuestionSR(report.getQuestionTotal() - report.getQuestionMC());
//            report.setQuestionPerPoll(report.getQuestionTotal() / report.getPollTotal());
//            
//            int responses = 0;
//                        
//            m_logger.info("5");
//            
//
//            sql = "SELECT COUNT (*) AS \"responseTotal\" FROM MC_RESPONSE";
//            results = DatabaseQuery(sql);
//            results.next();
//            responses = results.getInt("responseTotal");
//            sql = "SELECT COUNT (*) AS \"responseTotal\" FROM SR_RESPONSE";
//            results = DatabaseQuery(sql);
//            results.next();
//            report.setResponseTotal(results.getInt("responseTotal") + responses);
//            report.setResponsePerQuestion(report.getResponseTotal() / report.getQuestionTotal());
            
            m_logger.info("6");
            
            
            sql = "SELECT * FROM Users";
            results = DatabaseQuery(sql);
            
            m_logger.info("7");
            
            
            while(results.next()) {
                String type = results.getString("Type");
                if (type.equals("Web")) {
                    report.incUserWeb();
                    report.incUserTotal();
                    if (results.getString("Enabled").equals("Yes")) {
                        report.incUserWebActive();
                        report.incUserTotalActive();
                    }
                } else if (type.equals("Master")) {
                    report.incUserMaster();
                    report.incUserTotal();
                    if (results.getString("Enabled").equals("Yes")) {
                        report.incUserMasterActive();
                        report.incUserTotalActive();
                    }
                } else if (type.equals("Creator")) {
                    report.incUserCreator();
                    report.incUserTotal();
                    if (results.getString("Enabled").equals("Yes")) {
                        report.incUserCreatorActive();
                        report.incUserTotalActive();
                    }
                } else if (type.equals("Admin")) {
                    report.incUserAdmin();
                    report.incUserTotal();
                    if (results.getString("Enabled").equals("Yes")) {
                        report.incUserAdminActive();
                        report.incUserTotalActive();
                    }
                } else if (type.equals("SysAdmin")) {
                    report.incUserSysAdmin();
                    report.incUserTotal();
                    if (results.getString("Enabled").equals("Yes")) {
                        report.incUserSysAdminActive();
                        report.incUserTotalActive();
                    }
                }
            }
            return report;
        } catch (Exception Ex) {
            m_logger.info("getReportData() failed\n");
            m_logger.info(Ex.toString());
            return null;
        }
    }
    
    public ArrayList<ManagePA> getActivePAList (int userID) {
        String sql = "SELECT * FROM Users WHERE Type = 'Admin'";
        ManagePA details = new ManagePA();
        ArrayList<ManagePA> detailList = new ArrayList<ManagePA>();
        
        try {
            ResultSet results = DatabaseQuery(sql);
            
            while (results.next()) {
                details.setAdminID(results.getInt("User_ID"));
                if (results.getString("Enabled").equals("Yes")) {
                    details.setActive(true);
                } else {
                    details.setActive(false);
                }
                detailList.add(details);
            }
            return detailList;
        } catch (Exception Ex) {
            m_logger.info("getActivePAList");
            m_logger.info(Ex.toString());
            return null;
        }
    }
    
    public int[] displayActiveAdmin() {
        String sql = "SELECT User_ID FROM USERS WHERE Type = 'Admin' AND Enabled = 'Yes'";
        try {
            ResultSet results = DatabaseQuery(sql);
            int[] adminIDs = new int[2];
            int i = 0;
            while (results.next()) {
                adminIDs[i] = results.getInt("User_ID");
                i++;
            }
            return adminIDs;
        } catch (Exception Ex) {
            m_logger.info("getActivePAList");
            m_logger.info(Ex.toString());
            return null;
        }
    }
    
    public int[] displayInactiveAdmin() {
        String sql = "SELECT User_ID FROM USERS WHERE Type = 'Admin' AND Enabled = 'No'";
        try {
            ResultSet results = DatabaseQuery(sql);
            int[] adminIDs = new int[2];
            int i = 0;
            while (results.next()) {
                adminIDs[i] = results.getInt("User_ID");
                i++;
            }
            return adminIDs;
        } catch (Exception Ex) {
            m_logger.info("getActivePAList");
            m_logger.info(Ex.toString());
            return null;
        }
    }

    public boolean editUserExpiry(int userID, int expiry) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
